#encoding=utf-8
#Create by fuzongqiong 2018-3-9  Version V1.0
#description:
#   定义项目所有execl表格结构(前四行固定,所有表格固定结构)
#   第一行: 类名,用于定义程序中读取的名字,全英文,区分大小写,一旦确定不能更改
#   第二行: 类型名: int32, float 等,只支持基础类型
#   第三行: 解释说明用
#FileName: TablePythonDefine

import xdrlib,sys  
import xlrd  
import SplitFullFilePath
from CommonDef import *
        
G_TableTitleLines = 3;


    
        
class TableTitleInfo:
    __slots__ = [
    "TableName",
    "TitleName",
    "ExeclType",
    "ColumnIndex",
    "bKey"
    ];
    
    def __init__(self, TableName, ColumnIndex, TitleName, ExeclType, bKey, ColumnData):
        self.TableName = TableName;
        self.ColumnIndex = ColumnIndex;
        self.TitleName = TitleName;
        self.ExeclType = ExeclType;
        self.bKey = bKey;
        self.__CheckColumnDatasType(ColumnData);

    def GetTitleName(self):
        return self.TitleName;
        
    def GenerateCppMember(self):
        TypeObj = TypeMapDict[self.ExeclType];
        CppMemberName = TypeObj.CppType+" "+self.TitleName+";";
        if self.bKey:
            CppMemberName = CppMemberName +"// this is key";
        return CppMemberName+"\n";
        
    def GetCreateStatement(self):
        CreateStatement = self.TitleName+" "+TypeMapDict[self.ExeclType].SqliteType;
        if self.bKey:
            CreateStatement = CreateStatement+" PRIMARY KEY NOT NULL"
        return CreateStatement;
        
    def __CheckColumnDatasType(self, ColumnData):
        TypeObj = TypeMapDict[self.ExeclType];
        bCheckOk, ErrorRow = getattr(TypeObj, TypeObj.CheckFuncName)(ColumnData);
        if not bCheckOk:
            print("Error:TableName:%s cell(%d, %d) type error, CorrectType is %s, but the data is:%s" % (self.TableName, ErrorRow+G_TableTitleLines+1, self.ColumnIndex+1, self.ExeclType, ColumnData[ErrorRow]));
            sys.exit(0);
     


class TableProcessor:
    __slots__ = [
    "__FileFullPathName", 
    "__TableName", 
    "__XlsHandle",
    "__SheetHandle", 
    "__TableRowNum", 
    "__TableColumnNum",
    "__DBHandle",
    "__TitleInfos",
    "__RowDatas",
    "__InsertStatement"
    ];
    
    def __init__(self, FileFullPathName, DBHandle):
        self.__InitFileName(FileFullPathName);
        self.__InitDBHandle(DBHandle);
        self.__OpenExcel();
        self.__ParseTable();
        self.__InitInsertStatement();
    
    
    def GetTableName(self):
        return self.__TableName;
        
    def CreateDBTable(self):
        c = self.__DBHandle.cursor();
        StrCreateStatement = self.__GetCreateStatement();
        c.execute(StrCreateStatement);
        self.__DBHandle.commit();
        
    def WriteDatasToSqlite(self):
        c = self.__DBHandle.cursor();
        for ExeclRowData in self.__RowDatas:
            c.execute(self.__InsertStatement, ExeclRowData);
            
    def WriteStructsToDotH(self, FileHandle, LinePrefix=""):
        self.__WriteStructHead(FileHandle, LinePrefix);
        self.__WriteStructBody(FileHandle, LinePrefix+"\t");
        self.__WriteStructTail(FileHandle, LinePrefix);
    
    
    def __WriteStructHead(self, FileHandle, LinePrefix):
        StrLine = LinePrefix + "struct F" + self.__TableName + "\n";
        StrLine = StrLine + LinePrefix + "{\n";
        FileHandle.write(StrLine);
    
    def __WriteStructBody(self, FileHandle, LinePrefix):
        for ExeclTitle in self.__TitleInfos:
            StrLine = LinePrefix + ExeclTitle.GenerateCppMember();
            FileHandle.write(StrLine);

    def __WriteStructTail(self, FileHandle, LinePrefix):
        StrLine = LinePrefix + "};\n";
        FileHandle.write(StrLine);

    def __InitInsertStatement(self):
        self.__InsertStatement = "insert into "+self.__TableName+" values ( ";
        QuestionMarkArray = ["?" for x in range(0, self.__TableColumnNum)];
        StrQuestionMark = ",".join(QuestionMarkArray);
        self.__InsertStatement = self.__InsertStatement+StrQuestionMark+")";

    def __InitDBHandle(self, DBHandle):
        self.__DBHandle = DBHandle;
        
    def __InitFileName(self, __FileFullPathName):
        self.__FileFullPathName = __FileFullPathName;
        SplitFullFilePathObj = SplitFullFilePath.SplitFullFilePath(__FileFullPathName);
        self.__TableName = SplitFullFilePathObj.GetFileName();

    def __OpenExcel(self):  
        try:  
            self.__XlsHandle = xlrd.open_workbook(self.__FileFullPathName);
            self.__SheetHandle = self.__XlsHandle.sheet_by_index(0);
            self.__TableRowNum = self.__SheetHandle.nrows
            self.__TableColumnNum = self.__SheetHandle.ncols
        except Exception as e:  
            print("open xls error:", e);
            sys.exit(0);
        
    def __PraseTableTitles(self):
        self.__TitleInfos = [];
        for ColumnIndex in range(0, self.__TableColumnNum):
            _Title = self.__SheetHandle.cell_value(0, ColumnIndex);
            _ExeclType = self.__SheetHandle.cell_value(1, ColumnIndex);
            _bKey = ColumnIndex==0;
            _ColumnDatas = self.__SheetHandle.col_values(ColumnIndex)[G_TableTitleLines:self.__TableRowNum];
            if _Title and _ExeclType:
                TableTitleObj = TableTitleInfo(self.__TableName, ColumnIndex, _Title, _ExeclType, _bKey, _ColumnDatas);
                self.__TitleInfos.append(TableTitleObj);
            else:
                print("please check %s the cell (0, %d), (1, %d), may some of them is null or both!", self.__TableName, ColumnIndex, ColumnIndex) 
                sys.exit(0);
    
    def __PraseTableRowDatas(self):
        self.__RowDatas = [];
        for RowIndex in range(G_TableTitleLines, self.__TableRowNum):
            self.__RowDatas.append(self.__SheetHandle.row_values(RowIndex));
    
    def __ParseTable(self):
        self.__PraseTableTitles();
        self.__PraseTableRowDatas();
        
    def __GetCreateStatement(self):
        CreateStatement = "CREATE TABLE "+self.__TableName +"(";
        for TitleIndex in range(0, len(self.__TitleInfos)-1):
            CreateStatement = CreateStatement+self.__TitleInfos[TitleIndex].GetCreateStatement()+", ";
        CreateStatement = CreateStatement+self.__TitleInfos[len(self.__TitleInfos)-1].GetCreateStatement()+");";
        return CreateStatement;
        


        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
       

